/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package feverquery;

import java.io.Serializable;
import java.util.LinkedList;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;

/**
 *
 * @author Jose Luis
 */
public class FeverHelper implements Serializable {

    Session session = null;

    public FeverHelper() {
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
    }

    public List<Plans> getPlans() {
        List<Plans> plans = null;
        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();

            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from Plans");
            plans = (List<Plans>) q.list();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return plans;
    }

    public List<String> getContacts() {
        List<String> contacts = null;
        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();

            org.hibernate.Transaction tx = session.beginTransaction();
            //Query q = session.createQuery("select u from Users u where u.name='"+name+"' and surname='"+surname+"'");
            //Query q = session.createQuery("select distinct x.contacto from Listxml x order by x.contacto");
            Query q = session.createQuery("select distinct x.contacto from ExcelUsers x order by x.contacto");
            contacts = (List<String>) q.list();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return contacts;
    }

    public List<String> getGender() {
        List<String> gender = null;
        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();

            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("select distinct x.sexo from ExcelUsers x order by x.sexo");
            gender = (List<String>) q.list();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return gender;
    }

    public List<String> getStyle() {
        List<String> style = null;
        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();

            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("select distinct x.estilo from ExcelUsers x order by x.estilo");
            style = (List<String>) q.list();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return style;
    }

    public List<String> getInfluence() {
        List<String> influence = null;
        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();

            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("select distinct x.influencia from ExcelUsers x order by x.influencia");
            influence = (List<String>) q.list();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return influence;
    }

    public List<String> getImage() {
        List<String> image = null;
        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();

            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("select distinct x.imagen from ExcelUsers x order by x.imagen");
            image = (List<String>) q.list();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return image;
    }

    public List<String> getPlansName() {
        List<String> plans = null;
        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();

            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("select distinct x.planName from Plans x order by x.planName");
            plans = (List<String>) q.list();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return plans;
    }

    public List<ExcelUsers> getUserExcel(String plan, String name, String surname, String contact) {
        List<ExcelUsers> users = null;
        String p = "";

        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();

            org.hibernate.Transaction tx = session.beginTransaction();
            //Query q = session.createQuery("select u from Users u where u.name='"+name+"' and surname='"+surname+"'");
            Query q;

            if (plan.equals("")) {
                p = plan;
            } else {
                p = " AND x.plansid = '" + plan + "'";
            }

            if (contact == null || contact.equals("")) {
                if (name.equals("") && surname.equals("")) {
                    if (plan.equals("")) {
                        q = session.createQuery("select distinct x from ExcelUsers x order by x.facebookId");
                    } else {
                        q = session.createQuery("select distinct x from ExcelUsers x where x.plansid = '" + plan + "' order by x.facebookId");
                    }
                } else if (surname.equals("")) {
                    q = session.createQuery("select distinct x from ExcelUsers x where x.nombre='" + name + "'" + p + " order by x.facebookId");
                } else if (name.equals("")) {
                    q = session.createQuery("select distinct x from ExcelUsers x where x.apellidos='" + surname + "'" + p + " order by x.facebookId");
                } else {
                    q = session.createQuery("select distinct x from ExcelUsers x where x.nombre='" + name + "' and x.apellidos='" + surname + "'" + p + " order by x.facebookId");
                }
            } else {
                if (name.equals("") && surname.equals("")) {
                    q = session.createQuery("select distinct x from ExcelUsers x where x.contacto = '" + contact + "'" + p + " order by x.facebookId");
                } else if (surname.equals("")) {
                    q = session.createQuery("select distinct x from ExcelUsers x where x.nombre='" + name + "' and x.contacto = '" + contact + "'" + p + " order by x.facebookId");
                } else if (name.equals("")) {
                    q = session.createQuery("select distinct x from ExcelUsers x where x.apellidos='" + surname + "' and x.contacto = '" + contact + "'" + p + " order by x.facebookId");
                } else {
                    q = session.createQuery("select distinct x from ExcelUsers x where x.nombre='" + name + "' and x.apellidos= '" + surname + "' and x.contacto = '" + contact + "'" + p + " order by x.facebookId");
                }
            }
            users = (List<ExcelUsers>) q.list();
            boolean f = false;
            String fb = "";
            ExcelUsers ex, ex1;
            for (int i = 0; i < users.size(); i++) {
                ex = users.get(i);
                for (int j = i + 1; j < users.size(); j++) {
                    ex1 = users.get(j);
                    if (ex.getFacebookId().equals(ex1.getFacebookId()) && ex.getPlansid().equals(ex1.getPlansid())) {
                        users.remove(ex1);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return users;
    }

    public List<ExcelUsers> getUsersExcelInvitationsExact(String gender, String age1, String age2, List<String> style, String influence, List<String> image, String name, String lastname, String contact, String plan, boolean hidePlan) {
        List<ExcelUsers> users = null;
        String gend = "";
        String ag = "";
        String sty = "";
        String inf = "";
        String img = "";
        String first = "";
        String hide = "";

        if (gender.equals("")) {
            gend = "";
        } else {
            gend = "AND x.sexo = '" + gender + "' ";
            first = "x.sexo = '" + gender + "' ";
        }

        ag = "AND x.edad >= '" + age1 + "' AND x.edad <= '" + age2 + "'";
        if (first.equals("")) {
            first = "x.edad >= '" + age1 + "' AND x.edad <= '" + age2 + "'";
        }

        if (style == null || style.isEmpty()) {
            sty = "";
        } else {
            sty = "AND (x.estilo = '" + style.get(0) + "' ";
            for (int i = 1; i < style.size(); i++) {
                sty += "OR x.estilo = '" + style.get(i) + "' ";
            }
            sty += ")";
            //sty = "AND x.estilo = '" + style + "' ";
            if (first.equals("")) {
                sty = "(x.estilo = '" + style.get(0) + "' ";
                for (int i = 1; i < style.size(); i++) {
                    sty += "OR x.estilo = '" + style.get(i) + "' ";
                }
                sty += ")";
            }
        }

        if (influence.equals("")) {
            inf = "";
        } else {
            inf = "AND x.influencia = '" + influence + "' ";
            if (first.equals("")) {
                first = "x.influencia = '" + influence + "' ";
            }

        }

        if (image == null || image.isEmpty()) {
            img = "";
        } else {
            img = "AND (x.imagen = '" + image.get(0) + "' ";
            for (int i = 1; i < image.size(); i++) {
                img += "OR x.imagen = '" + image.get(i) + "' ";
            }
            img += ")";
            //sty = "AND x.estilo = '" + style + "' ";
            if (first.equals("")) {
                img = "(x.imagen = '" + image.get(0) + "' ";
                for (int i = 1; i < image.size(); i++) {
                    img += "OR x.imagen = '" + image.get(i) + "' ";
                }
                img += ")";
            }
        }

        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();

            org.hibernate.Transaction tx = session.beginTransaction();
            //Query q = session.createQuery("select u from Users u where u.name='"+name+"' and surname='"+surname+"'");
            Query q;
            if (contact == null || contact.equals("")) {
                if (name.equals("") && lastname.equals("")) {
                    if (first.equals("")) {
                        q = session.createQuery("select x from ExcelUsers x order by x.nombre,x.apellidos,x.facebookId");
                    } else {
                        q = session.createQuery("select x from ExcelUsers x where " + first + gend + ag + sty + inf + img + "order by x.nombre,x.apellidos,x.facebookId");
                    }
                } else if (lastname.equals("")) {
                    q = session.createQuery("select x from ExcelUsers x where x.nombre = '" + name + "' " + gend + ag + sty + inf + img + "order by x.nombre,x.apellidos,x.facebookId");
                } else if (name.equals("")) {
                    q = session.createQuery("select x from ExcelUsers x where x.apellidos = '" + lastname + "' " + gend + ag + sty + inf + img + "order by x.nombre,x.apellidos,x.facebookId");
                } else {
                    q = session.createQuery("select x from ExcelUsers x where x.nombre = '" + name + "' and x.apellidos = '" + lastname + "' " + gend + ag + sty + inf + img + "order by x.nombre,x.apellidos,x.facebookId");
                }
            } else {
                if (name.equals("") && lastname.equals("")) {
                    q = session.createQuery("select x from ExcelUsers x where x.contacto = '" + contact + "' " + gend + ag + sty + inf + img + "order by x.nombre,x.apellidos,x.facebookId");
                } else if (lastname.equals("")) {
                    q = session.createQuery("select x from ExcelUsers x where x.nombre = '" + name + "' and x.contacto = '" + contact + "' " + gend + ag + sty + inf + img + "order by x.nombre,x.apellidos,x.facebookId");
                } else if (name.equals("")) {
                    q = session.createQuery("select x from ExcelUsers x where x.apellidos = '" + lastname + "' and x.contacto = '" + contact + "' " + gend + ag + sty + inf + img + "order by x.nombre,x.apellidos,x.facebookId");
                } else {
                    q = session.createQuery("select x from ExcelUsers x where x.nombre = '" + name + "' and x.apellidos = '" + lastname + "' and x.contacto = '" + contact + "' " + gend + ag + sty + inf + img + "order by x.nombre,x.apellidos,x.facebookId");
                }
            }

            users = (List<ExcelUsers>) q.list();
            ExcelUsers ex, ex1;
            String fb = "";
            boolean found = false;
            List<ExcelUsers> aux = new LinkedList<ExcelUsers>();
            for (int i = 0; i < users.size(); i++) {
                ex = users.get(i);
                fb = ex.getFacebookId();
                for (int j = i + 1; j < users.size(); j++) {
                    ex1 = users.get(j);
                    if (fb.equals(ex1.getFacebookId())) {
                        boolean a = !plan.equals("");
                        boolean b = ex1.getPlansid().equals(plan) || ex.getPlansid().equals(plan);
                        if (hidePlan && a && b) {
                            aux.add(ex);
                        }
                        aux.add(ex1);
                    }
                }
            }
            users.removeAll(aux);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return users;
    }

    List getUsersExcelInvitationsNotExact(String gender, String age1, String age2, List<String> style, String influence, List<String> image, String name, String lastname, String contact, String plan, boolean hidePlan) {
        List<ExcelUsers> users = null;
        String gend = "";
        String ag = "";
        String sty = "";
        String inf = "";
        String img = "";
        String first = "";

        if (gender.equals("")) {
            gend = "";
        } else {
            gend = "AND x.sexo = '" + gender + "' ";
            first = "x.sexo = '" + gender + "' ";
        }

        ag = "AND x.edad >= '" + age1 + "' AND x.edad <= '" + age2 + "'";
        if (first.equals("")) {
            first = "x.edad >= '" + age1 + "' AND x.edad <= '" + age2 + "'";
        }

        if (style == null || style.isEmpty()) {
            sty = "";
        } else {
            sty = "AND (x.estilo = '" + style.get(0) + "' ";
            for (int i = 1; i < style.size(); i++) {
                sty += "OR x.estilo = '" + style.get(i) + "' ";
            }
            sty += ")";
            //sty = "AND x.estilo = '" + style + "' ";
            if (first.equals("")) {
                sty = "(x.estilo = '" + style.get(0) + "' ";
                for (int i = 1; i < style.size(); i++) {
                    sty += "OR x.estilo = '" + style.get(i) + "' ";
                }
                sty += ")";
            }
        }

        if (influence.equals("")) {
            inf = "";
        } else {
            inf = "AND x.influencia = '" + influence + "' ";
            if (first.equals("")) {
                first = "x.influencia = '" + influence + "' ";
            }

        }

        if (image == null || image.isEmpty()) {
            img = "";
        } else {
            img = "AND (x.imagen = '" + image.get(0) + "' ";
            for (int i = 1; i < image.size(); i++) {
                img += "OR x.imagen = '" + image.get(i) + "' ";
            }
            img += ")";
            //sty = "AND x.estilo = '" + style + "' ";
            if (first.equals("")) {
                img = "(x.imagen = '" + image.get(0) + "' ";
                for (int i = 1; i < image.size(); i++) {
                    img += "OR x.imagen = '" + image.get(i) + "' ";
                }
                img += ")";
            }
        }

        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();

            org.hibernate.Transaction tx = session.beginTransaction();
            //Query q = session.createQuery("select u from Users u where u.name='"+name+"' and surname='"+surname+"'");
            Query q;
            if (contact == null || contact.equals("")) {
                if (name.equals("") && lastname.equals("")) {
                    if (first.equals("")) {
                        q = session.createQuery("select x from ExcelUsers x order by x.nombre,x.apellidos,x.facebookId");
                    } else {
                        q = session.createQuery("select x from ExcelUsers x where " + first + gend + ag + sty + inf + img + "order by x.nombre,x.apellidos,x.facebookId");
                    }
                } else if (lastname.equals("")) {
                    q = session.createQuery("select x from ExcelUsers x where x.nombre like '%" + name + "%' " + gend + ag + sty + inf + img + "order by x.nombre,x.apellidos,x.facebookId");
                } else if (name.equals("")) {
                    q = session.createQuery("select x from ExcelUsers x where x.apellidos like '%" + lastname + " and x.nombre like '%" + name + "%' " + gend + ag + sty + inf + img + "order by x.nombre,x.apellidos,x.facebookId");
                } else {
                    q = session.createQuery("select x from ExcelUsers x where x.nombre like '%" + name + "%' and x.apellidos like '%" + lastname + "%' " + gend + ag + sty + inf + img + "order by x.nombre,x.apellidos,x.facebookId");
                }
            } else {
                if (name.equals("") && lastname.equals("")) {
                    q = session.createQuery("select x from ExcelUsers x where x.contacto = '" + contact + "' x.nombre like '%" + name + "%' " + gend + ag + sty + inf + img + "order by x.nombre,x.apellidos,x.facebookId");
                } else if (lastname.equals("")) {
                    q = session.createQuery("select x from ExcelUsers x where x.nombre like '%" + name + "%' and x.contacto = '" + contact + "' and x.nombre like '%" + name + "%' " + gend + ag + sty + inf + img + "order by x.nombre,x.apellidos,x.facebookId");
                } else if (name.equals("")) {
                    q = session.createQuery("select x from ExcelUsers x where x.apellidos like '%" + lastname + "%' and x.contacto = '" + contact + "' and like '%" + name + "%' " + gend + ag + sty + inf + img + "order by x.nombre,x.apellidos,x.facebookId");
                } else {
                    q = session.createQuery("select x from ExcelUsers x where x.nombre like '%" + name + "%' and x.apellidos like '%" + lastname + "%' and x.contacto = '" + contact + "' " + gend + ag + sty + inf + img + "order by x.nombre,x.apellidos,x.facebookId");
                }
            }

            users = (List<ExcelUsers>) q.list();
            ExcelUsers ex, ex1;
            String fb = "";
            boolean found = false;
            List<ExcelUsers> aux = new LinkedList<ExcelUsers>();
            for (int i = 0; i < users.size(); i++) {
                ex = users.get(i);
                fb = ex.getFacebookId();
                for (int j = i + 1; j < users.size(); j++) {
                    ex1 = users.get(j);
                    if (fb.equals(ex1.getFacebookId())) {
                        boolean a = !plan.equals("");
                        boolean b = ex1.getPlansid().equals(plan) || ex.getPlansid().equals(plan);
                        if (hidePlan && a && b) {
                            aux.add(ex);
                        }
                        aux.add(ex1);
                    }
                }
            }
            users.removeAll(aux);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return users;
    }

    long getNumbPlans() {
        Long num = new Long(0);
        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("select x.idplans from Plans x order by x.idplans desc");
            q.setMaxResults(1);
            List<Integer> l = q.list();
            int s = l.get(0);
            num = new Long(s);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }

    void addPlan(String plan, long num) {
        Plans p = null;
        Query q;
        session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        try {
            if (!plan.equals("")) {
                p = new Plans();
                p.setIdplans((int) num);
                p.setPlanName(plan);
                session.save(p);
                tx.commit();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }/* finally {
         session.close();
         }*/
    }

    long getNumContacs() {
        long num = 0;
        try {
            session = HibernateUtil.getSessionFactory().getCurrentSession();
            org.hibernate.Transaction tx = session.beginTransaction();
            /*Query q = session.createQuery("select count(x) from ExcelUsers x");
             num = (Long) q.list().get(0);*/
            Query q = session.createQuery("select x.excelUsersId from ExcelUsers x order by x.excelUsersId desc");
            q.setMaxResults(1);
            List<Integer> l = q.list();
            int s = l.get(0);
            num = new Long(s);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }

    void update(long id, String nombre, String apellidos, String sexo, int edad, String estilo, int influencia, int imagen, String facebookId, String colegio, String universidad, String trabajo, String zona, String otros, String eMail, String telefono, String plan, String contacto) {
        ExcelUsers e = null;
        Query q;
        session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        try {
            if (!plan.equals("")) {
                e = new ExcelUsers();
                e.setExcelUsersId((int) id);
                e.setNombre(nombre);
                e.setApellidos(apellidos);
                e.setSexo(sexo);
                e.setEdad(edad);
                e.setEstilo(estilo);
                e.setInfluencia(influencia);
                e.setImagen(imagen);
                e.setColegio(colegio);
                e.setUniversidad(universidad);
                e.setTrabajo(trabajo);
                e.setZona(zona);
                e.setOtros(otros);
                e.setEMail(eMail);
                e.setTelefono(telefono);
                e.setPlansid(plan);
                e.setFacebookId(facebookId);
                e.setContacto(contacto);
                session.save(e);
                tx.commit();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}
